package dao;

import pojo.user;
import util.DBUtil;
import util.C3P0Util;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class UserDao2 {

    //新增用户
    public int insert(user user) throws ClassNotFoundException, SQLException {
        Class.forName("oracle.jdbc.OracleDriver");
        Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL"
                , "system"
                , "000000");
        String sql="insert into j_user values (seq_j_user.nextval,'"+user.getUsername()+"','"+user.getPassword()+"',1)";
        Statement statement = connection.createStatement();
        statement.executeUpdate(sql);
        statement.close();
        connection.close();
        return 0;
    }
    public int update(user user ) throws ClassNotFoundException, SQLException {
        if(null==user.getUsername()){
            user.setUsername("");
        }
        Connection connection = C3P0Util.getConnection();
        String sql="update j_user set username=?,password=?  where id=?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1,user.getUsername());
        statement.setString(2,user.getPassword());
        statement.setInt(3,user.getId());

        statement.executeUpdate();

        C3P0Util.close(statement,connection);
        return 0;
    }
    public int delete(user user) throws ClassNotFoundException, SQLException {
        Class.forName("oracle.jdbc.OracleDriver");
        Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL"
                , "system",
                "000000");
        String sql="delete from j_user where id=?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setInt(1,user.getId());
        statement.execute();
        statement.close();
        connection.close();
        return 0;
    }
    public List<user> select() throws ClassNotFoundException, SQLException {
        //1.加载驱动类
        //2.创建连接 获取连接对象
        //3,创建SQL语句
        //4.执行sql语句
        List <user> userList=new ArrayList<>();
        Connection connection = DBUtil.getConnection();
        String sql = "select * from j_user";
        PreparedStatement statement = connection.prepareStatement(sql);
        //statement.setInt(1, id);
        ResultSet resultSet = statement.executeQuery();
        while(resultSet.next()){
            int id = resultSet.getInt("id");
            String username = resultSet.getString("username");
            String password = resultSet.getString("password");
            int state = resultSet.getInt("state");
            //System.out.println("id:"+i+" username:"+username+ " password:"+password+" state:"+state);
            //将值存入user对象
            user user=new user(id,username,password,state);
            userList.add(user);
        }
        DBUtil.close(connection,statement,resultSet);
        return userList;
    }
    public List selectAll() throws SQLException, ClassNotFoundException {
        List<user> userList=new ArrayList<>();
        Connection connection = C3P0Util.getConnection();
        String sql = "select * from j_user";
        PreparedStatement statement = connection.prepareStatement(sql);
        //statement.setInt(1, id);
        ResultSet resultSet = statement.executeQuery();
        while(resultSet.next()){
            int id = resultSet.getInt("id");
            String username = resultSet.getString("username");
            String password = resultSet.getString("password");
            int state = resultSet.getInt("state");
            //System.out.println("id:"+i+" username:"+username+ " password:"+password+" state:"+state);
            //将值存入user对象
            user user=new user(id,username,password,state);
            userList.add(user);
        }
        C3P0Util.close(connection,statement,resultSet);
        return userList;
    }
    public List select03(int id1) throws SQLException, ClassNotFoundException {
        List<user> userList=new ArrayList<>();
        Connection connection = C3P0Util.getConnection();
        String sql = "select * from j_user where id=?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setInt(1, id1);
        ResultSet resultSet = statement.executeQuery();
        while(resultSet.next()){
            int id = resultSet.getInt("id");
            String username = resultSet.getString("username");
            String password = resultSet.getString("password");
            int state = resultSet.getInt("state");
            //System.out.println("id:"+i+" username:"+username+ " password:"+password+" state:"+state);
            //将值存入user对象
            user user=new user(id,username,password,state);
            userList.add(user);
        }
        C3P0Util.close(connection,statement,resultSet);
        return userList;
    }

}
